﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using fqlmoney.Model;
using MySql.Data.MySqlClient;

namespace fqlmoney.DAL
{
    public class OrderDAL
    {

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="extsql">sql条件</param>
        /// <param name="order">排序</param>
        /// <param name="pageIndex">页数</param>
        /// <param name="pageCount">条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <returns></returns>
        public List<user_order> GetPage(string extsql, string order, int pageIndex, int pageCount, ref int totalPages, ref int totalItems)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" SELECT user_order.*,user_moneytype.`t_name` FROM `user_order` ");
            sql.Append(" LEFT JOIN `user_moneytype` ON user_moneytype.t_id =`user_order`.`t_id` WHERE 1=1 ");
            sql.Append(extsql);
            if (!string.IsNullOrEmpty(order))
            {
                sql.Append(" order by " + order);
            }
            sql.Append(" limit " + ((pageIndex - 1) * pageCount) + "," + pageCount + ";");
            sql.Append("SELECT COUNT(*) FROM  user_order LEFT JOIN `user_moneytype` ON user_moneytype.t_id =`user_order`.`t_id` WHERE 1=1 " + extsql + ";");
            return DBUtility.MySqlHelper.ExecuteDataReaderPage<user_order>(sql.ToString(), pageCount, ref totalPages, ref totalItems);
        }

        /// <summary>
        /// 获取扇形图数据
        /// </summary>
        /// <param name="extsql"></param>
        /// <returns></returns>
        public List<dynamic> GetPieData(string extsql)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT user_order.`t_id`,t_name,SUM(user_order.`o_amount`) AS amount FROM `user_order` ");
            sql.Append(" LEFT JOIN user_moneytype ON user_order.`t_id`=`user_moneytype`.`t_id` ");
            sql.Append("  WHERE 1=1 ");
            sql.Append(extsql);
            sql.Append(" GROUP BY user_order.`t_id` ");
            return DBUtility.MySqlHelper.ExecuteDynamic(sql.ToString());
        }

        /// <summary>
        /// 添加分类
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(user_order model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@u_id",model.u_id),
                new MySqlParameter("@t_id",model.t_id),
                new MySqlParameter("@o_state",model.o_state),
                 new MySqlParameter("@o_time",model.o_time),
                  new MySqlParameter("@o_note",model.o_note),
                   new MySqlParameter("@o_amount",model.o_amount)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("INSERT INTO `user_order`(`u_id`,`t_id`,`o_state`,`o_time`,`o_note`,`o_amount`)VALUES(@u_id,@t_id,@o_state,@o_time,@o_note,@o_amount)", para);
        }

        /// <summary>
        /// 更新分类
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(user_order model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@t_id",model.t_id),
                new MySqlParameter("@o_id",model.o_id),
                new MySqlParameter("@o_state",model.o_state),
                 new MySqlParameter("@o_time",model.o_time),
                  new MySqlParameter("@o_note",model.o_note),
                   new MySqlParameter("@o_amount",model.o_amount)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("UPDATE user_order SET `t_id`=@t_id,`o_state`=@o_state,`o_time`=@o_time,`o_note`=@o_note,`o_amount`=@o_amount WHERE `o_id`=@o_id", para);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public int Delete(int id,int uid)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@id",id),
                new MySqlParameter("@uid",uid)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("DELETE FROM user_order WHERE u_id=@uid and `o_id`=@id", para);
        }

    }
}
